﻿#I @"..\FsMongo"
#I @"C:\ws\Snippets\Misc"
#r "Microsoft.Office.Interop.Excel"
#load "FsBson.fs"
#load "FsMgProtocol.fs"
#load "FsMgDriver.fs"
#load "ComMonad.fs"

open ComMonad
open Microsoft.Office.Interop.Excel;;
open System
open System.Text

let path = @"C:\Users\Fai\Documents\Ford\ET\Aster\bom\bomsolve\Exp_LOU.xls"

let excelData() =
    com {
        let w = ApplicationClass(Visible=true)
        let wb = w.Workbooks
        let e = wb.Open(Filename=path, UpdateLinks=false, Editable=false)
        try
            let ws = e.Worksheets
            let s = ws.["Data"] :?> Worksheet
            let usedRange = s.UsedRange
            let values = usedRange.Value2 :?> obj[,]
            return values
        finally
            e.Close(SaveChanges=false)
            wb.Close()
            w.Quit()
        } |> Com.Run

let d = excelData()

type Lou = 
    {Pn:string; Name:string; CPSC:string; CPSCSec:string; 
    PT:string; VL:string;  PF:string; MF:string; Q:string; 
    EffIn:string; EffOut:string}

let trim (o:obj) = match o with :? string as s -> s.Trim() | _ -> ""
let v (d:obj[,]) i j = d.[i,j] |> trim
let changeDashToUnderscore (s:string) = s.Replace("-","_")

let createRecords (d:obj[,])  =
    let endRow = d.GetUpperBound(0)
    [for i in 2..endRow ->
        {Pn = sprintf "%s-%s-%s" (v d i 1) (v d i 2) (v d i 3)
         Name = v d i 4
         CPSC  = v d i 5
         CPSCSec = v d i 6
         PT = v d i 7
         VL = v d i 8
         PF = v d i 9
         MF = v d i 10 |> changeDashToUnderscore
         Q = v d i 12
         EffIn = v d i 13
         EffOut = v d i 14 } ]

let rs = createRecords d

let chunk n xs = seq { 
    let i = ref 0 
    let arr = ref <| Array.create n (Unchecked.defaultof<'a>) 
    for x in xs do 
        if !i = n then  
            yield !arr 
            arr := Array.create n (Unchecked.defaultof<'a>) 
            i := 0  
        (!arr).[!i] <- x 
        i := !i + 1 
    if !i <> 0 then 
        yield (!arr).[0..!i-1] } 

open FsBson
open FsMgProtocol
open FsMgDriver

//map of table column names and functions to extract values for those columns
let cmap =
    [
     "pn", (fun l -> l.Pn)
     "name", (fun l -> l.Name)
     "cpsc", (fun l -> l.CPSC)
     "cpscsec", (fun l -> l.CPSCSec)
     "pt", (fun l -> l.PT)
     "vl", (fun l -> l.VL)
     "pf", (fun l -> l.PF)
     "mf", (fun l -> l.MF)
     "q", (fun l -> l.Q)
     "effin", (fun l -> l.EffIn)
     "effout", (fun l -> l.EffOut)
     ]

rs
|> chunk 20
|> Seq.map (fun chk -> chk |> Array.map (fun lou -> cmap |> List.map (fun (k,f) -> k, f lou) |> bson))
|> Seq.iter (fun chk -> insertC "test.bom" chk)

queryC {defQ with Collection="test.bom"; Take=1000}